package org.s170.dao;

import org.s170.entity.Note;
import org.s170.utils.Dbutil;
import org.s170.utils.PagerUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NoteDao {

    //查询所有笔记文章信息
    public List<Note> getAllNote(){
        List<Note> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement psmt =null;
        ResultSet rs =null ;
        Note note =null;
        try {
            String sql="select * from notes";
            conn = Dbutil.getConnection();
            psmt =conn.prepareStatement(sql);
            rs=psmt.executeQuery();
            while (rs.next()){
                note  = new Note();
                note.setNoteid(rs.getInt(1));
                note.setTid(rs.getString(2));
                note.setTypename(rs.getString(3));
                note.setTitle(rs.getString(4));
                note.setPubTime(rs.getTimestamp(5));
                note.setUserName(rs.getString(6));
                note.setContent(rs.getString(7));
                note.setZstxt(rs.getString(8));
                note.setTyped(rs.getString(9));
                note.setUistop(rs.getInt(10));
                note.setIstop(rs.getInt(11));
                note.setEyes(rs.getInt(12));
                note.setComment(rs.getInt(13));
                note.setLike(rs.getInt(14));
                note.setStatus(rs.getInt(15));
                note.setImages(rs.getString(16));

                list.add(note);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Dbutil.closeAll(conn,psmt,rs);
        }
            return  list;
    }

    //找单个笔记信息
    public  Note getNidFindNote(String noteid){
        Connection conn = null;
        PreparedStatement psmt =null;
        ResultSet rs =null ;
        Note note =null;
        try {
            String sql="select * from notes where notesid=?";
            conn = Dbutil.getConnection();
            psmt =conn.prepareStatement(sql);
            psmt.setString(1,noteid);
            rs=psmt.executeQuery();
            while (rs.next()){
                note  = new Note();
                note.setNoteid(rs.getInt(1));
                note.setTid(rs.getString(2));
                note.setTypename(rs.getString(3));
                note.setTitle(rs.getString(4));
                note.setPubTime(rs.getTimestamp(5));
                note.setUserName(rs.getString(6));
                note.setContent(rs.getString(7));
                note.setZstxt(rs.getString(8));
                note.setTyped(rs.getString(9));
                note.setUistop(rs.getInt(10));
                note.setIstop(rs.getInt(11));
                note.setEyes(rs.getInt(12));
                note.setComment(rs.getInt(13));
                note.setLike(rs.getInt(14));
                note.setStatus(rs.getInt(15));
                note.setImages(rs.getString(16));

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Dbutil.closeAll(conn,psmt,rs);
        }
        return  note;
    }



    //添加笔记文章
    public  int addNote(Note note){
        String sql = "insert into notes values(?,?,?,default,?,?,?,?,?,default,default,default,default,default,?)";

        Object [] obj = {
             note.getTid(),
             note.getTypename(),
             note.getTitle(),
             note.getUserName(),
             note.getContent(),
             note.getZstxt(),
             note.getTyped(),
             note.getUistop(),
             note.getImages()
        };

        int row = Dbutil.executeUpdate(sql,obj);

        return  row;
    }

    ////------------分页的方法-----------------------

        //注：在此方法中，只要把PagerUtil对象中的五个属性封装好就OK了
        public PagerUtil<Note> selectAllByPaer(int currentPage, int pageCount) {
            PagerUtil<Note> pager = new PagerUtil<Note>();

            List<Note> list = new ArrayList<Note>();

            // 注：以下三行代码必须遵循顺序
            pager.setTotalRecords(getRowCount()); // 设置总记录数
            pager.setPageCount(pageCount); // 设置每页显示记录数
            pager.setCurrentPage(currentPage); // 设置当前页

            //pager.getTotalPageCount();   //计算总页数
            //pager.setTotalPageCount(pager.getTotalRecords(),pageCount);
            // 注：总页数不能由用户手动设置,而通过公式计算

            // 第一：获取连接对象
            Connection conn = Dbutil.getConnection();

            // 第二：定义操作数据库的SQL语句
            StringBuilder sb = new StringBuilder();
            sb.append("select * from ( ");
            sb.append("	select *, row_number() over(order by a.notesid asc) as rownumber from notes as a ");
            sb.append(") as b ");
            sb.append("where rownumber between  ? and ?");

            String sql = sb.toString();

            PreparedStatement pstmt = null;
            ResultSet rs = null;
            // 第三：获取语句对象
            try {
                pstmt = conn.prepareStatement(sql);

                // 第四：填充数据
                // (当前页数-1)*每页记录数+1
                pstmt.setInt(1, (pager.getCurrentPage() - 1) * pager.getPageCount() + 1);
                // 当前页数 * 每页记录数
                pstmt.setInt(2, pager.getCurrentPage() * pager.getPageCount());

                // 第五：执行SQL语句
                rs = pstmt.executeQuery();

                // 第六：循环迭代
                while (rs.next()) {
                    // 1.创建实体对象
                    Note note = new Note();

                    note.setNoteid(rs.getInt(1));
                    note.setTid(rs.getString(2));
                    note.setTypename(rs.getString(3));
                    note.setTitle(rs.getString(4));
                    note.setPubTime(rs.getTimestamp(5));
                    note.setUserName(rs.getString(6));
                    note.setContent(rs.getString(7));
                    note.setZstxt(rs.getString(8));
                    note.setTyped(rs.getString(9));
                    note.setUistop(rs.getInt(10));
                    note.setIstop(rs.getInt(11));
                    note.setEyes(rs.getInt(12));
                    note.setComment(rs.getInt(13));
                    note.setLike(rs.getInt(14));
                    note.setStatus(rs.getInt(15));
                    note.setImages(rs.getString(16));

                    list.add(note);

                }

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                Dbutil.closeAll(conn,pstmt,rs);
            }

            // 把list封装到PagerUtil对象中
              pager.setData(list);

              pager.getTotalPageCount();
    //        System.out.println(pager.getTotalPageCount());
    //        System.out.println(pager);

            return pager;
        }


        //返回总记录数
        public int getRowCount() {
            int count = 0;

            // 第一：获取连接对象
            Connection conn = Dbutil.getConnection();
            // 第二：定义操作数据库的SQL语句
            String sql = "select count(*) from notes";
            // 第三：获取语句对象
            PreparedStatement pstmt = null;
            ResultSet rst = null;

            try {
                pstmt = conn.prepareStatement(sql);

                // 第四：填充数据

                // 第五：执行SQL语句
                rst = pstmt.executeQuery();

                // 遍历结果集中的数据，把数据封装在List对象中，从而进行传递
                // 注：不能返回结果集，否则破坏了Dao的封装性
                if (rst.next()) {
                    count = rst.getInt(1);
                }

            } catch (SQLException e) {

                e.printStackTrace();
            } finally {
                // 第六：关闭对象
                Dbutil.closeAll(conn, pstmt, rst);
            }

            return count;
        }



        public int getRowCount(String cond) {
            int count = 0;

            // 第一：获取连接对象
            Connection conn = Dbutil.getConnection();
            // 第二：定义操作数据库的SQL语句
            String sql = "select COUNT(*) from notes where title like '%'+?+'%'";
            // 第三：获取语句对象
            PreparedStatement pstmt = null;
            ResultSet rst = null;

            try {
                pstmt = conn.prepareStatement(sql);

                // 第四：填充数据
                pstmt.setString(1, "%" + cond + "%");

                // 第五：执行SQL语句
                rst = pstmt.executeQuery();

                // 遍历结果集中的数据，把数据封装在List对象中，从而进行传递
                // 注：不能返回结果集，否则破坏了Dao的封装性
                if (rst.next()) {
                    count = rst.getInt(1);
                }

            } catch (SQLException e) {

                e.printStackTrace();
            } finally {
                // 第六：关闭对象
                Dbutil.closeAll(conn, pstmt, rst);
            }

            return count;
        }

        public PagerUtil<Note> selectAllByPaer(int currentPage, int pageCount, String cond) {
            PagerUtil<Note> pager = new PagerUtil<Note>();
            List<Note> list = new ArrayList<Note>();

            // 注：以下三行代码必须遵循顺序
            pager.setTotalRecords(getRowCount(cond)); // 设置总记录数，注：必须是条件查询的总记录数
            pager.setPageCount(pageCount); // 设置每页显示记录数
            pager.setCurrentPage(currentPage); // 设置当前页
            // 注：总页数不能由用户手动设置,而通过公式计算

            // 第一：获取连接对象
            Connection conn = Dbutil.getConnection();

            // 第二：定义操作数据库的SQL语句
            StringBuilder sb = new StringBuilder();

            sb.append("select * from ( ");
            sb.append("select *, row_number() over(order by a.notesid asc) as rownumber from notes as a  where title like '%'+?+'%'");
            sb.append(") as b ");
            sb.append("where rownumber between  ? and ?");

            String sql = sb.toString();

            PreparedStatement pstmt = null;
            ResultSet rs = null;
            // 第三：获取语句对象
            try {
                pstmt = conn.prepareStatement(sql);

                // 第四：填充数据
                pstmt.setString(1, "%" + cond + "%");
                // (当前页数-1)*每页记录数+1
                pstmt.setInt(2, (pager.getCurrentPage() - 1) * pager.getPageCount() + 1);
                // 当前页数 * 每页记录数
                pstmt.setInt(3, pager.getCurrentPage() * pager.getPageCount());

                // 第五：执行SQL语句
                rs = pstmt.executeQuery();

                // 第六：循环迭代
                while (rs.next()) {
                    // 1.创建实体对象
                    Note note = new Note();


                    note.setNoteid(rs.getInt(1));
                    note.setTid(rs.getString(2));
                    note.setTypename(rs.getString(3));
                    note.setTitle(rs.getString(4));
                    note.setPubTime(rs.getTimestamp(5));
                    note.setUserName(rs.getString(6));
                    note.setContent(rs.getString(7));
                    note.setZstxt(rs.getString(8));
                    note.setTyped(rs.getString(9));
                    note.setUistop(rs.getInt(10));
                    note.setIstop(rs.getInt(11));
                    note.setEyes(rs.getInt(12));
                    note.setComment(rs.getInt(13));
                    note.setLike(rs.getInt(14));
                    note.setStatus(rs.getInt(15));
                    note.setImages(rs.getString(16));

                    list.add(note);

                }

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                Dbutil.closeAll(conn, pstmt, rs);
            }

            // 把list封装到PagerUtil对象中
            pager.setData(list);

            return pager;
        }


    public static void main(String[] args) {
        NoteDao noteDao =new NoteDao();
        List<Note> list =noteDao.getAllNote();
        for (Note note : list) {
            System.out.println(note);
        }

       // Note note =new Note("0","日常","巨无霸","划船","麦当劳真香","hhh","原创",0,"f4.jpeg");
       // int row=noteDao.addNote(note);
    }

}
